set echo off
--------------------------------------------------------------------------------
-- @name: prof_wait
-- @author: dion cho
-- @note: profile wait event
-- @usage: @prof_wait 1000 151 %buffer% "p1,p2"
--------------------------------------------------------------------------------

define __INTERVAL = &1
define __SID = "&2"
define __WAITEVENT = "&3"
define __COLUMNS = "&4"


col sid format 9999
col event format a30
col p1 format 99999
col p2 format 99999
col p3 format 99999
col wait_time head "wait|time(ms)" format 9999999.99
col wait_pct head "wait(%)" format 99
col avg_wait_time head "avg wait|time(ms)" format 9999999.99


col start_time new_value start_time
select dbms_utility.get_time as start_time from dual;

select /*+ ordered */
  sid,
  event,
  &__COLUMNS,
  trunc(100*(event_hits/total_hits)) as wait_ptc,
  1000*&__INTERVAL * (event_hits/total_hits) as wait_time,
  1000*&__INTERVAL * (dist_hits/total_hits) as avg_wait_time
from 
  (
    select /*+ ordered use_nl(w) */
      w.sid,
      w.event,
      &__COLUMNS, 
      count(*) as event_hits,
      count(distinct seq#) as dist_hits,
      max(lvl) as total_hits
    from 
      (select /*+ no_merge */ level as lvl from dual connect by level <= 10000000000 
          and (dbms_utility.get_time - &start_time) <= &__INTERVAL * 100) t1,
      (select /*+ no_merge */
          *
       from v$session_wait
          where sid in (&__SID) 
            and event like '%&__WAITEVENT%') w
    group by
      w.sid, w.event, &__COLUMNS
    order by 
      count(*) desc
  )
;

set echo on
